﻿using System;
using Microsoft.SqlServer.Management.Trace;
using Microsoft.SqlServer.Management.Common;
using System.ComponentModel;

namespace com.ContrivedExample.SQLTools
{
    public delegate void TraceEventHandler(object sender, TraceEventArgs e);

    /// <summary>
    /// A very basic SQL Server tracer, like SQL Profiler.
    /// Useful for simply watching what is happening for SQL commands
    /// on the server and you do not have access to Profiler.
    /// </summary>
    public class Tracer
    {
        /// <summary>
        /// This event is fired each time SQL Server performs an operation.
        /// Subscribe to this to receive the trace data.
        /// </summary>
        public event TraceEventHandler TraceUpdated;

        /// <summary>
        /// Event fired if an Exception ocurrs in the background worker so
        /// the UI thread can inspect it.
        /// </summary>
        public event EventHandler BackgroundError;

        /// <summary>
        /// Set true to filter events to the NTUserName
        /// </summary>
        public bool UsesNTUserName { get; set; }

        /// <summary>
        /// When UsesNTUserName is true, only events initiated by this Windows User
        /// will display.
        /// </summary>
        public string NTUserName { get; set; }

        /// <summary>
        /// Set true to only receive SQL:BatchStarting and SQL:BatchCompleted events.
        /// </summary>
        public bool UsesBatch { get; set; }

        /// <summary>
        /// Set true to use the credentials of the logged in windows user for
        /// database access.
        /// </summary>
        public bool UsesIntegratedSecurity { get; set; }

        /// <summary>
        /// The SQL Server login name to use if UsesIntegratedSecurity is false.
        /// </summary>
        public string UserName { get; set; }

        /// <summary>
        /// The SQL Server login password to use when UsesIntegratedSecurity is false.
        /// </summary>
        public string Password { get; set; }

        private const string SQLBATCHSTART = "SQL:BatchStarting";
        private const string SQLBATCHEND = "SQL:BatchCompleted";
        private const string TRACEFILE = "traceer.trc";

        private BackgroundWorker bgw;
        TraceServer rdr;
        private bool TraceRunning;
        private string ServerName;

        /// <summary>
        /// Creates a Tracer.
        /// </summary>
        /// <param name="Servername">The SQL Server instance you want to profile.</param>
        public Tracer(string Servername)
        {
            TraceRunning = false;

            rdr = new TraceServer();

            ServerName = Servername;

            bgw = new BackgroundWorker();
            bgw.WorkerReportsProgress = true;
            bgw.WorkerSupportsCancellation = true;
            bgw.DoWork += new DoWorkEventHandler(bgw_DoWork);
            bgw.ProgressChanged += new ProgressChangedEventHandler(bgw_ProgressChanged);
            bgw.RunWorkerCompleted += bgw_RunWorkerCompleted;
            
        }

        void bgw_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            if (e.Error != null)
            {
                if (BackgroundError != null)
                {
                    BackgroundError(sender, e);
                }
            }
        }

        void bgw_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            if (TraceUpdated != null)
            {
                TraceData td = e.UserState as TraceData;
                if (TraceUpdated != null)
                {
                    TraceUpdated(this, new TraceEventArgs(td));
                }
            }
        }

        void bgw_DoWork(object sender, DoWorkEventArgs e)
        {
            try
            {
                SqlConnectionInfo coninfo = new SqlConnectionInfo();

                if (!string.IsNullOrEmpty(ServerName))
                {
                    coninfo.ServerName = ServerName;
                }

                if (UsesIntegratedSecurity)
                    coninfo.UseIntegratedSecurity = true;
                else
                {
                    if (string.IsNullOrWhiteSpace(UserName))
                        throw new ArgumentException("UserName was not supplied for SQL Server Authentication");
                    else
                        coninfo.UserName = this.UserName;

                    if (string.IsNullOrWhiteSpace(Password))
                        throw new ArgumentException("Password was not supplied for SQL Server Authentication");
                    else
                        coninfo.Password = this.Password;
                }

                rdr.InitializeAsReader(coninfo, TRACEFILE);
                TraceRunning = true;

                while (rdr.Read())
                {
                    if (bgw.CancellationPending)
                        break;

                    TraceData td = new TraceData
                    {
                        ApplicationName = rdr.IsDBNull(rdr.GetOrdinal("ApplicationName")) ? "" : rdr["ApplicationName"].ToString(),
                        ClientProcessID = rdr.IsDBNull(rdr.GetOrdinal("ClientProcessID")) ? "" : rdr["ClientProcessID"].ToString(),
                        CPU = rdr.IsDBNull(rdr.GetOrdinal("CPU")) ? "" : rdr["CPU"].ToString(),
                        Duration = rdr.IsDBNull(rdr.GetOrdinal("Duration")) ? "" : rdr["Duration"].ToString(),
                        EndTime = rdr.IsDBNull(rdr.GetOrdinal("EndTime")) ? "" : rdr["EndTime"].ToString(),
                        EventClass = rdr.IsDBNull(rdr.GetOrdinal("EventClass")) ? "" : rdr["EventClass"].ToString(),
                        LoginName = rdr.IsDBNull(rdr.GetOrdinal("LoginName")) ? "" : rdr["LoginName"].ToString(),
                        NTUserName = rdr.IsDBNull(rdr.GetOrdinal("NTUserName")) ? "" : rdr["NTUserName"].ToString(),
                        Reads = rdr.IsDBNull(rdr.GetOrdinal("Reads")) ? "" : rdr["Reads"].ToString(),
                        SPID = rdr.IsDBNull(rdr.GetOrdinal("SPID")) ? "" : rdr["SPID"].ToString(),
                        StartTime = rdr.IsDBNull(rdr.GetOrdinal("StartTime")) ? "" : rdr["StartTime"].ToString(),
                        TextData = rdr.IsDBNull(rdr.GetOrdinal("TextData")) ? "" : rdr["TextData"].ToString(),
                        Writes = rdr.IsDBNull(rdr.GetOrdinal("Writes")) ? "" : rdr["Writes"].ToString()
                    };

                    if (UsesNTUserName)
                    {
                        if (td.NTUserName != this.NTUserName)
                            continue;
                    }

                    if (UsesBatch)
                    {
                        if (!(td.EventClass == SQLBATCHSTART || td.EventClass == SQLBATCHEND))
                            continue;
                    }

                    bgw.ReportProgress(0, td);
                }

                if (bgw.CancellationPending)
                    StopTrace();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                StopTrace();
                TraceRunning = false;
            }
        }

        private bool ValidateInput()
        {
            //TODO: actually validate this
            return true;
        }

        /// <summary>
        /// Stops a running trace.
        /// </summary>        
        private void StopTrace()
        {
            if (TraceRunning)
            {
                rdr.Stop();
                rdr.Close();
                TraceRunning = false;
            }
        }
        
        /// <summary>
        /// Starts a trace running
        /// </summary>
        /// <exception cref="Microsoft.SqlServer.Management.Trace.SqlTraceException">
        /// General exception raised by the SMO trace objects.</exception>
        public void Start()
        {
            if (TraceRunning)
                return;
            else
                if (ValidateInput())
                {                    
                    bgw.RunWorkerAsync();                   
                }
        }

        /// <summary>
        /// Stops the trace. Trace will stop on next read.
        /// </summary>
        /// <exception cref="Microsoft.SqlServer.Management.Trace.SqlTraceException">
        /// General exception raised by the SMO trace objects.</exception>
        public void Stop()
        {
            bgw.CancelAsync();
        }
    }


    /// <summary>
    /// A class encapsulating all of the data sent from a SQL trace event.
    /// see: http://msdn.microsoft.com/en-us/library/aa173882(v=sql.80).aspx
    /// </summary>
    public class TraceData
    {
        /// <summary>
        /// 
        /// </summary>
        public string EventClass { get; set; }
        
        /// <summary>
        /// Text of the statement that is about to be executed.
        /// </summary>
        public string TextData { get; set; }

        /// <summary>
        /// Name of the client application that created the connection to an instance of SQL Server. 
        /// This column is populated with the values passed by the application rather than the 
        /// displayed name of the program. 
        /// </summary>
        public string ApplicationName { get; set; }

        /// <summary>
        /// Windows user name.
        /// </summary>
        public string NTUserName { get; set; }

        /// <summary>
        /// Name of the login of the user (either SQL Server security login or the 
        /// Microsoft Windows login credentials in the form of DOMAIN\username).
        /// </summary>
        public string LoginName { get; set; }

        /// <summary>
        /// The amount of CPU time used by the event in milliseconds
        /// </summary>
        public string CPU { get; set; }

        /// <summary>
        /// Reads represent the number of 8kb "page reads" performed by SQL Server 
        /// to execute any given query.
        /// </summary>
        public string Reads { get; set; }

        /// <summary>
        /// Number of physical disk writes performed by the server on behalf of the event.
        /// </summary>
        public string Writes { get; set; }

        /// <summary>
        /// The duration of an event in microseconds (one millionth, or 10-6, of a second)
        /// </summary>
        public string Duration { get; set; }

        /// <summary>
        /// ID assigned by the host computer to the process where the 
        /// client application is running. This data column is populated 
        /// if the client provides the client process ID. 
        /// </summary>
        public string ClientProcessID { get; set; }

        /// <summary>
        /// ID of the session on which the event occurred.
        /// </summary>
        public string SPID { get; set; }

        /// <summary>
        /// Time at which the event started, if available.
        /// </summary>
        public string StartTime { get; set; }
        public string EndTime { get; set; }
    }

    /// <summary>
    /// Event arg object will hold a TraceData object.
    /// </summary>
    public class TraceEventArgs : EventArgs
    {
        public TraceData EventTraceData { get; set; }

        public TraceEventArgs(TraceData td)
        {
            this.EventTraceData = td;
        }
    }
}
